﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mysoft.Map.MVC;
using System.Data;
using Mysoft.Map.Extensions.DAL;
using Mysoft.QuickCode.Newtonsoft.Json;

namespace Mysoft.Test.Services
{
    public class DLService
    {
        [Action]
        public string List()
        {
            DataTable table1 = CPQuery.From("select *, 0 as TotalScore from t_User").FillDataTable();
            DataTable table2 = CPQuery.From("select * from t_Score").FillDataTable();
            List<string> courseNames = CPQuery.From("select distinct CourseName from t_Score").FillScalarList<string>();
            DataTable mgrTable = new DataTable();
            foreach (DataColumn col in table1.Columns)
            {
                mgrTable.Columns.Add(col.ColumnName);
            }
            //动态列
            foreach (string name in courseNames)
            {
                mgrTable.Columns.Add(name);
            }
            foreach (DataRow t1Row in table1.Rows)
            {
                DataRow newRow = mgrTable.NewRow();
                foreach (DataColumn t1Col in table1.Columns)
                {
                    newRow[t1Col.ColumnName] = t1Row[t1Col.ColumnName];
                }
                foreach (string cname in courseNames)
                {
                    DataRow[] rows = table2.Select("UserName='" + t1Row["UserName"].ToString() + "' and CourseName='" + cname + "'");
                    newRow[cname] = rows.Length == 0 ? "0" : rows[0]["ScoreResult"];
                }
                mgrTable.Rows.Add(newRow);
            }

            return JsonConvert.SerializeObject(new
            {
                rows = mgrTable,
                total = mgrTable.Rows.Count
            });
        }

    }
}
